#科协数据清洗
import pymysql
import re

from bs4 import BeautifulSoup


def etlBiaoZhang():
    sql="select id,表彰全文,bzjl_section,ext1 from 表彰奖励 "
    cur.execute(sql)
    datas=cur.fetchall()

    for data in datas:
        id=data[0]
        content=data[1]
        bzjl=data[2]
        ext1=data[3]
        soup1 = BeautifulSoup(content,'lxml')
        paras1=soup1.find_all('para')
        cons=[]
        for para in paras1:
            para=para.get_text()
            if '<' not in para:
                cons.append(para.replace('\n',''))
        cons='\n'.join(cons)

        soup3 = BeautifulSoup(bzjl,'lxml')
        paras3=soup3.find_all('para')
        bzjl=[]
        for para in paras3:
            para=para.get_text()
            if '<' not in para:
                bzjl.append(para.replace('\n',''))
        bzjl='\n'.join(bzjl)
        if ext1 != None:
            soup2=BeautifulSoup(ext1,'lxml')
            paras2=soup2.find_all('para')
            exts=[]

            for para in paras2:
                para=para.get_text()
                if '<' not in para:
                    exts.append(para.replace('\n',''))
            exts='\n'.join(exts)
            #print(cons)
            update="update 表彰奖励 set 表彰全文=%s,bzjl_section=%s,ext1=%s where id=%s"
            value=(cons,bzjl,exts,id)
            cur.execute(update,value)
            conn.commit()
        else:
            update="update 表彰奖励 set 表彰全文=%s,bzjl_section=%s where id=%s"
            value=(cons,bzjl,id)
            cur.execute(update,value)
            conn.commit()

def etlDiFangKeXie():
    sql="select id,与会主要人员,会议全文,`section` from 地方科协"
    cur.execute(sql)
    datas=cur.fetchall()
    for data in datas:
        id=data[0]
        human=data[1]
        content=data[2]
        section=data[3]
        if content:
            soup1 = BeautifulSoup(content,'lxml')
            paras1=soup1.find_all('para')
            cons=[]
            for para in paras1:
                para=para.get_text()
                if '<' not in para:
                    cons.append(para.replace('\n',''))
            cons='\n'.join(cons)

        soup2 = BeautifulSoup(section,'lxml')
        title2=soup2.find_all('title')
        paras2=soup2.find_all('para')
        sec=[]
        if title2:
            title=title2[0].get_text()
            sec.append(title)
        for para in paras2:
            para=para.get_text()
            if '<' not in para:
                sec.append(para.replace('\n',''))
        sec='\n'.join(sec)
        #print(sec)
        if human:
            human=list(human)
            human[0]=''
            human[1]=''
            human[2]=''
            human[3]=''
            human[4]=''
            human=''.join(human)
            human=human.replace(';<br>',';')
            print(human)
            update="update 地方科协 set 与会主要人员=%s,会议全文=%s,`section`=%s where id=%s"
            value=(human,cons,sec,id)
            cur.execute(update,value)
            conn.commit()
        else:
            update="update 地方科协 set 会议全文=%s,`section`=%s where id=%s"
            value=(cons,sec,id)
            cur.execute(update,value)
            conn.commit()

def etlLingDaoJiangHua():
    sql="select id,讲话全文,`section` from 领导讲话"
    cur.execute(sql)
    datas=cur.fetchall()
    for data in datas:
        id=data[0]
        content=data[1]
        section=data[2]
        if content:
            soup1 = BeautifulSoup(content,'lxml')
            paras1=soup1.find_all('para')
            cons=[]
            for para in paras1:
                para=para.get_text()
                if '<' not in para:
                    cons.append(para.replace('\n',''))
            cons='\n'.join(cons)
        if section:
            soup2 = BeautifulSoup(section,'lxml')
            paras2=soup2.find_all('para')
            sec=[]
            for para in paras2:
                para=para.get_text()
                if '<' not in para:
                    sec.append(para.replace('\n',''))
            sec='\n'.join(sec)
        update="update 领导讲话 set 讲话全文=%s,`section`=%s where id=%s"
        value=(cons,sec,id)
        cur.execute(update,value)
        conn.commit()

def etlNianDuShiJian():
    sql="select id,会议全文,`section` from 全国协会 where 会议全文 is null"
    cur.execute(sql)
    datas=cur.fetchall()
    for data in datas:
        id=data[0]
        #place=data[1]
        content=data[1]
        section=data[2]
        # if place:
        #     soup = BeautifulSoup(place,'lxml')
        #     paras=soup.find_all('para')
        #     pla=[]
        #     for para in paras:
        #         para=para.get_text()
        #         if '<' not in para:
        #             pla.append(para.replace('\n',''))
        #     pla='\n'.join(pla)

        # if content:
        #     soup1 = BeautifulSoup(content,'lxml')
        #     paras1=soup1.find_all('para')
        #     cons=[]
        #     for para in paras1:
        #         para=para.get_text()
        #         if '<' not in para:
        #             cons.append(para.replace('\n',''))
        #     cons='\n'.join(cons)

        if section:
            soup2 = BeautifulSoup(section,'lxml')
            title2=soup2.find_all('title')
            paras2=soup2.find_all('para')
            sec=[]
            if title2:
                for tit in title2:

                    title=tit.get_text()
                    sec.append(title)
            if paras2:
                for para in paras2:
                    para=para.get_text()
                    if '<' not in para:
                        sec.append(para.replace('\n',''))
            sec='\n'.join(sec)
            # if len(sec)>0:
            #     print(str(id)+'|'+sec)
        # if place:
        # if content:
        if section:
            update="update 全国协会 set `section`=%s where id=%s"
            value=(sec,id)
            cur.execute(update,value)
            conn.commit()
            # else:
            #     update="update 全国协会 set `section`=%s where id=%s"
            #     value=(sec,id)
            #     cur.execute(update,value)
            #     conn.commit()
            # else:
            #     update="update 重要文件 set 地区=%s where id=%s"
            #     value=(pla,id)
            #     cur.execute(update,value)
            #     conn.commit()


def etlSection():
    sql="select id,`section` from 重要文件 where `section` is not null "
    cur.execute(sql)
    datas=cur.fetchall()
    pat=r'<(.*?)>'
    for data in datas:
        id=data[0]
        section=data[1]
        section=section.replace('\n','').replace('</para><para>','\n')
        d=re.findall(pat,section)
        for a in d:
            section=section.replace(a,'')
        section=section.replace('<','').replace('>','').replace('/','')
        update="update 重要文件 set `section`=%s where id=%s"
        value=(section,id)
        cur.execute(update,value)
        conn.commit()

def etlHuman():
    sql="select id,与会主要人员 from 全国协会 where 与会主要人员 is not null"
    cur.execute(sql)
    datas=cur.fetchall()
    for data in datas:
        id=data[0]
        human=data[1]
        if human:
            human=list(human)
            human[0]=''
            human[1]=''
            human[2]=''
            human[3]=''
            human[4]=''
            human=''.join(human)
            human=human.replace(';<br>',';')
        update="update 全国协会 set 与会主要人员=%s where id=%s"
        value=(human,id)
        cur.execute(update,value)
        conn.commit()

if __name__ == '__main__':
    try:
        conn = pymysql.Connect(host = '10.170.128.56',
                                     port = 3306,
                                     user = 'root',
                                     passwd = '123456',
                                     db = 'science',
                                     charset='utf8mb4')
        cur = conn.cursor()

        etlNianDuShiJian()

    finally:

        cur.close()
        conn.close()